package com.cttic.erp.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.cttic.erp.bean.BusiManHourBean;
import com.cttic.erp.bean.InfoProjectBean;

public interface BusiManHourMapper {


	@Select("select * from busi_man_hour "
			+ "where employee_id = #{employee_id} order by end_date desc")
	public List<BusiManHourBean> getBeans(@Param("employee_id") long employee_id);
	
	
	
	@Insert("insert into busi_man_hour(id, employee_id, begin_date, end_date, isTB,isSP,week_id, real_begin_date, real_end_date) "
			+ "value(#{busiManHourBean.id}, #{busiManHourBean.employee_id}, #{busiManHourBean.begin_date}, #{busiManHourBean.end_date}, #{busiManHourBean.isTB}, #{busiManHourBean.isSP}, #{busiManHourBean.week_id}, #{busiManHourBean.real_begin_date}, #{busiManHourBean.real_end_date})")
	public void insertBean(@Param("busiManHourBean") BusiManHourBean busiManHourBean);
	

	@Delete("delete from busi_man_hour where id = #{id} ")
	public int delete(@Param("id") long id);


	@Select("select t.record_id,t.begin_date ,t.end_date from busi_weekly_report_record t where t.end_date >#{startDate} and t.record_id not IN ( select week_id from busi_man_hour o where o.end_date > #{startDate}) order by t.end_date desc ")
	public List<Map<String, String>> getPeriod(@Param("startDate") String startDate);


	@Select("select * from busi_man_hour "
			+ " where id = #{id} order by end_date desc")
	public List<BusiManHourBean> getBeansById(@Param("id") long id);


	@Select("select * from info_project p where p.prj_id in (   select DISTINCT(t.result_value) prj_id from busi_weekly_report_result t   where t.record_id=#{week_id} and t.result_type='TASK' and t.result_code='PRJ_ID'   )")
	public List<InfoProjectBean> getProjectsByWeekId(@Param("week_id") String week_id);


	@Select("   SELECT  max(t.result_value) end_date  FROM      busi_weekly_report_result t     WHERE     t.record_id=#{week_id}   AND t.result_type='TASK'    AND t.result_code='END_TIME' ")
	public String getRealEndDateByWeekId(@Param("week_id") String week_id);


	@Select("  SELECT  min(t.result_value) begin_date  FROM      busi_weekly_report_result t     WHERE   t.record_id=#{week_id}    AND t.result_type='TASK'   AND t.result_code='BEGIN_TIME'  ")
	public String getRealBeginDateByWeekId(@Param("week_id") String week_id);


	@Select("  select o.id,e.employee_name employee_name,o.begin_date,o.end_date,o.isTB,o.isSP,o.week_id from busi_man_hour o ,info_employee e where o.isSP = '0'  and o.employee_id = e.employee_id ")
	public List<BusiManHourBean> getApprovalBeans();


	@Select("  select * from busi_man_hour t where t.week_id=#{record_id} ")
	public List<BusiManHourBean> getBeanByWeekId(@Param("record_id") String record_id);


	@Select(" select w.begin_date,w.end_date,o.isTB,o.isSP,w.record_id week_id ,o.id from busi_weekly_report_record w  left JOIN busi_man_hour o   on w.begin_date=o.begin_date and w.end_date=o.end_date  and w.record_id = o.week_id WHERE  w.employee_id=#{employee_id}  order by w.begin_date desc  ")
	public List<BusiManHourBean> getAllBeans(long employee_id);


	@Select("  select DISTINCT SUBSTRING(end_date,1,4) as years  from portal.busi_man_hour t ORDER by years desc ")
	public List<Map<String, String>> findYears();


	@Select("  select  DISTINCT t.begin_date,t.end_date from portal.busi_man_hour t where t.end_date like #{year}  order by t.end_date desc ; ")
	public List<Map<String, String>> getWeekByYear(String year);


	@Select("  select o.id,e.employee_name employee_name,o.begin_date,o.end_date,o.isTB,o.isSP,o.week_id  from busi_man_hour o ,info_employee e  where o.employee_id = e.employee_id  and o.end_date=#{end_date}  ORDER BY o.isSP, e.employee_id ")
	public List<BusiManHourBean> getApprovalBeansByEndDate(String end_date);


	@Update(" update busi_man_hour d set d.isSP = 1 where d.id=#{id} ")
	public void approval(long id);
	
}
